package com.power.sifa.approve.dao;

import com.power.sifa.approve.model.Apl_main;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.io.Serializable;
import java.util.List;
import java.util.Map;

/**
 * 业务申请主表 持久层(hibernate)
 */

public interface Apl_mainDao extends JpaRepository<Apl_main, Serializable>, JpaSpecificationExecutor<Apl_main>, Repository<Apl_main, Serializable>, Apl_mainDaoCustom<Apl_main, Serializable> {

    @Modifying
    @Transactional
    @Query("delete  from Apl_main s where  s.id in (:ids) ")
    void removeByIds(@Param("ids") List<Integer> ids);

    @Query(" from Apl_main s where  s.instid = :instid ")
    Apl_main getByInstid(@Param("instid") String instid);

    @Query(" from Apl_main s where  s.instid in (:instids) ")
    List<Apl_main> getByInstids(@Param("instids") List<String> instids);

    @Modifying
    @Transactional
    @Query("update Apl_main s set s.scatid = :scatid where  s.instid = :instid ")
    void updateScatid(@Param("instid") String instid, @Param("scatid") String scatid);

    /**
     * 更新主表状态
     *
     * @param id
     * @param status
     */
    @Modifying
    @Transactional
    @Query("update Apl_main s set s.status = :status where  s.id = :id ")
    void updateStatus(@Param("id") String id, @Param("status") Integer status);

    /**
     * 更新主表状态和办结时间
     *
     * @param id
     * @param status
     * @param finishtime
     */
    @Modifying
    @Transactional
    @Query("update Apl_main s set s.status = :status, s.finishtime = :finishtime where  s.id = :id ")
    void updateStatusAndFinishtime(@Param("id") String id, @Param("status") Integer status, @Param("finishtime") String finishtime);

    /**
     * 更新业务主表状态和备份异常状态
     *
     * @param id
     * @param status
     */
    @Modifying
    @Transactional
    @Query("update Apl_main s set s.status = :status, s.statuscp = :statuscp where  s.id = :id ")
    void updateStatusAndCp(@Param("id") String id, @Param("status") Integer status, @Param("statuscp") Integer statuscp);

    /**
     * 案件号去重
     *
     * @param acceptcode
     * @return
     */
    @Query(value = "select count(1) from Apl_main s where s.acceptcode = :acceptcode and s.id != :id and s.matid = :matid and s.applycode is not null group by s.acceptcode", nativeQuery = true)
    Integer countAcceptcode(String id,  String acceptcode, String matid);

    /**
     * 根据流程实例id批量更新状态
     *
     * @param status
     * @param instids
     */
    @Modifying
    @Transactional
    @Query("update Apl_main s set s.status = :status where s.instid in (:instids) ")
    void updateStatusByInstid(@Param("status") Integer status, @Param("instids") List<String> instids);


    /**
     * 通过案号获取主数据
     * @param acceptcode
     * @return
     */
    @Query(value = "select s.* from Apl_main s left join Apl_admre_audit b on s.id = b.id where (s.acceptcode = :acceptcode or s.acceptcode like :acceptcodelike) and b.code is null order by acceptcode", nativeQuery = true)
    List<Apl_main> getOneByAcceptcode(@Param("acceptcode") String acceptcode,
                                 @Param("acceptcodelike") String acceptcodelike);

    /**
     * 更新主表受理时间
     *
     * @param id
     * @param accepttime
     */
    @Modifying
    @Transactional
    @Query("update Apl_main s set s.accepttime = :accepttime where  s.id = :id ")
    void updateAccepttime(@Param("id") String id, @Param("accepttime") String accepttime);


    /**
     * 根据状态统计主流程总数
     * @param status
     */
    @Query(value = "select count(1) from apl_main a left join apl_admreview b on a.id = b.id where b.id is not null and a.status in (:status)", nativeQuery = true)
    Integer countByStatus(List<String> status);

    /**
     * 根据userid和状态统计我完成的业务总数
     * @param userid
     * @param status
     * @return
     */
    @Query(value = "select count(1) from ( SELECT  * FROM ( SELECT row_number() OVER (PARTITION BY act_hi_taskinst.proc_inst_id_ ORDER BY act_hi_taskinst.id_ DESC) AS rk, * FROM act_hi_taskinst) temp WHERE temp.rk = 1 and assignee_ = :userid) a left join apl_main b on a.proc_inst_id_ = b.instid where b.applycode is not null and b.status in (:status)", nativeQuery = true)
    Integer countByUserAndStatus(String userid, List<String> status);


    /**
     * 根据状态查询记录
     * @param status
     * @return
     */
    @Query(value = "select a.id, a.reguserid, a.acceptcode, b.applyname, b.targetname from apl_main a left join apl_admreview b on a.id = b.id where b.id is not null and a.status = :status", nativeQuery = true)
    List<Map<String, Object>> queryListByStatus(Integer status);

    /**
     * 查询完成状态列表
     * @param status
     * @return
     */
    @Query(value = "SELECT * FROM apl_main WHERE applycode is not null and finishtime is not null and status IN (:status);", nativeQuery = true)
    List<Apl_main> getListByStatus(List<String> status);
}
